# -*- coding: utf-8 -*-
"""
Created on Fri Apr  3 10:05:07 2020
基金统计-统计各家基金公司旗下的各类型基金 和 规模
@author: lfp
"""

import pandas as pd
import numpy as np

from app.repository.MF_FundArchives import fund_company_lfp
from app.repository.MF_InvestAdvisorOutline import advisor
from app.repository.fund_information import get_fund_size_lfp
from app.repository.fund_type import get_first_type_lfp

if __name__ == '__main__':
    # 全部基金类型
    first_type = get_first_type_lfp()
    first_type = first_type.query("ListedState == 1")
    # 全部基金规模
    fund_size = get_fund_size_lfp()
    # 拼接信息
    df = first_type[['InnerCode', 'first_class_type']].merge(fund_size[['InnerCode', 'size']])
    # 基金与基金公司信息
    fcl = fund_company_lfp()
    advise_name = advisor()
    df_1 = fcl.merge(advise_name, on='InvestAdvisorCode')
    df_f = df.merge(df_1)

    # 个数
    ss = df_f.groupby(['InvestAdvisorName', 'first_class_type'])['InnerCode'].count().reset_index()
    ss2 = df_f.groupby(['InvestAdvisorName', 'first_class_type'])['size'].sum().reset_index()
    # 结果
    result = ss.merge(ss2, on=['InvestAdvisorName', 'first_class_type'])
    result = result.query("size > 0")
    result.columns = ['基金公司名称','基金类型','基金个数','基金规模汇总']
    result.to_excel("fund_company_stats.xls",encoding='gbk')
